#!/usr/bin/python
# -*- coding: utf-8 -*-

#       This program is free software; you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation; either version 2 of the License, or
#       (at your option) any later version.
#
#       This program is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#
#       You should have received a copy of the GNU General Public License
#       along with this program; if not, write to the Free Software
#       Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#       MA 02110-1301, USA.

from config import mysql

database = mysql().db

# ALTER TABLE `repair_type` ADD `customer` INT NULL AFTER `currency`;
# UPDATE repair_type SET `customer` = '1' WHERE `customer` is NULL;

query_msptable = ("SELECT `id`, `code`, `description`, `unit`, "
                  "`description_rus`, `integer`, `model`, `safety_stock`,"
                  " `planning_type`, `planning_condition` FROM  `spare_parts`")
query_unitlist = "SELECT * FROM  `unit`"
query_unitbyid = "SELECT `descr` FROM `unit` WHERE id='%s'"
query_idbyunit = "SELECT `id` FROM `unit` WHERE `descr`='%s'"
query_idbycurrency = "SELECT `id` FROM `currency` WHERE `currency`='%s'"
query_idbymodel = "SELECT `id` FROM `model` WHERE `name`='%s'"
query_idbycustomer = "SELECT `id` FROM `customer` WHERE `name`='%s'"
query_idbydestination = "SELECT `id` FROM `destination` WHERE `daddr`='%s'"
query_currencybyid = "SELECT `currency` FROM `currency` WHERE id='%s'"
query_modelsbyid = ("SELECT `t2`.`name` FROM `model_swap` as t1, `model` as t2"
                    " WHERE `t1`.`model_id` = `t2`.`id` and `t1`.`select_id`"
                    " = '%s'")
query_currencylist = "SELECT * FROM `currency`"
query_modellist = "SELECT * FROM  `model`"
query_model_swaplist = "SELECT * FROM  `model_swap`"
query_addmsp = ("INSERT INTO `spare_parts` (`id`, `code`, `description`, "
                "`unit`, `description_rus` , `integer`, `price`, `currency`, "
                "`model`, `safety_stock`, `planning_type`, "
                "`planning_condition`) VALUES (NULL, '%s', \"%s\", '%s', "
                "\"%s\", '%s', NULL, NULL, '%s', '%s', '%s', '%s');")
query_msplinebyid = ("SELECT `id`, `code`, `description`, `unit`, "
                     "`description_rus` , `integer`, `model`, `safety_stock`,"
                     " `planning_type`, `planning_condition` FROM "
                     " `spare_parts` WHERE `id` = '%s';")
query_imodelbysid = ("SELECT `model_id` FROM `model_swap` WHERE"
                     " `select_id` = '%s';")
query_addmspupdate = ("UPDATE  `spare_parts` SET  `description` = \"%s\","
                      " `unit` = '%s', `description_rus` = \"%s\","
                      " `integer` = '%s', `price` = NULL, `currency` = NULL,"
                      " `model` = '%s', `safety_stock` = '%s', `planning_type`"
                      " = '%s', `planning_condition` = '%s' WHERE"
                      "  `code` = '%s';")
query_clearswaplistbyid = "DELETE FROM `model_swap` WHERE `select_id` = '%s';"
query_addmodel_swap = "INSERT INTO `model_swap` (`id`, `select_id`, `model_id`) VALUES (NULL, '%s', '%s');"
query_addUnit = "INSERT INTO `unit` (`id`, `descr`) VALUES (NULL, '%s');"
query_addCurrency = "INSERT INTO `currency` (`id`, `currency`) VALUES (NULL, '%s');"
query_addModel = "INSERT INTO `model` (`id`, `name`) VALUES (NULL, '%s');"
query_getSizeDialog = "SELECT `left`,`top`,`width`,`height` FROM `dialog_size` WHERE `configName` = '%s' AND `dialogName` = '%s';"
query_insertZerroSize = "INSERT INTO `dialog_size` (`id`, `configName`, `dialogName`, `left`, `top`, `width`, `height`) VALUES (NULL, '%s', '%s', '0', '0', '0', '0');"
query_updateSize = "UPDATE `dialog_size` SET `left` = '%s', `top` = '%s', `width` = '%s', `height` = '%s' WHERE `configName` = '%s' AND `dialogName` = '%s';"
query_autoRepairCode = "SHOW TABLE STATUS FROM `" + database + "` LIKE 'repair_type';"
query_spcode = "SELECT `id` , `code`, `description` FROM `spare_parts` ORDER BY  `spare_parts`.`code` ASC"
query_spcodetable = "SELECT `t1`.`id`, `t2`.`code`, `t2`.`description`, `t1`.`quantity` FROM `spcodetable` as t1, `spare_parts` as t2 WHERE `t1`.`spcode` = `t2`.`id` AND `t1`.`guid` = '%s';"
query_spcodetablefull = "SELECT `w2`.`code`, `w2`.`description`, `w2`.`quantity`, `w1`.`group`, `w1`.`gprice` FROM (SELECT `t3`.`code`,`t3`.`group`, COALESCE(`t4`.`pricing`, 0) as `gprice` FROM (SELECT `t1`.`code`, `t1`.`description`, `t2`.`group`, `t2`.`id` FROM `spare_parts` as `t1`, `customer_group` as `t2`) as `t3` LEFT JOIN `pricing` as `t4` ON (`t3`.`code` = `t4`.`spcode` AND `t3`.`id` = `t4`.`group`)) AS `w1`, (SELECT `t2`.`code`, `t2`.`description`, `t1`.`quantity` FROM `spcodetable` as `t1`, `spare_parts` as `t2` WHERE `t1`.`guid` = (SELECT `SparePartsConsumption` FROM `repair_type` WHERE `code` = '%s') AND `t1`.`spcode` = `t2`.`id`) AS `w2` WHERE `w1`.`code` = `w2`.`code`;"
query_addSPCode = "INSERT INTO `spcodetable` (`id`, `guid`, `spcode`, `quantity`) VALUES (NULL, '%s', '%s', '%s');"
query_delSPCodeByID = "DELETE FROM `spcodetable` WHERE `id` = '%s';"
query_delmrtmodel = "DELETE FROM `mrtmodel` WHERE `mrtid` = '%s';"
query_getmrtmodelids = "SELECT `modelid` FROM `mrtmodel` WHERE `mrtid` = %s;"
query_addmrtmodel = "INSERT INTO `mrtmodel` (`mrtid`, `modelid`) VALUES (%s, %s);"
query_addRepairType = "INSERT INTO `repair_type` (`code`, `repairType`, `repairTypeRus`, `SparePartsConsumption`, `model`, `RepairTypePrice`, `SparePartsPrice`, `currency`, `customer`)VALUES(NULL , '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');"
query_customerGroup = "SELECT * FROM `customer_group`;"
query_addCustomerGroup = "INSERT INTO `customer_group` (`id`, `group`)VALUES(NULL , '%s');"
query_addCustomer = "INSERT INTO `customer` (`id`, `name`, `group`)VALUES(NULL, '%s', '%s');"
query_addDestination = "INSERT INTO `destination` (`id`, `daddr`)VALUES(NULL, '%s');"
query_getRepairType = "SELECT `t1`.`code`, `t1`.`repairType`, `t1`.`repairTypeRus`, `t1`.`SparePartsConsumption`, `t2`.`name` as `model`, `t1`.`RepairTypePrice`, `t1`.`SparePartsPrice`, `t3`.`currency` as `currency`, `t4`.`name` as `customer` FROM `repair_type` as `t1`, (SELECT `x1`.`mrtid`, GROUP_CONCAT(`x1`.`mname`) as `name` FROM (SELECT `mrtid`, (select `z1`.`name` FROM `model` as `z1` where `z1`.`id` = `x2`.`modelid`) as `mname` FROM `mrtmodel` as `x2`) as `x1` GROUP BY `x1`.`mrtid`) as `t2`, `currency` as t3, `customer` as t4 WHERE `t1`.`model` = `t2`.`mrtid` AND `t1`.`currency` = `t3`.`id` AND `customer` = `t4`.`id`;"
query_getRepairTypeFilter = "SELECT `t1`.`code`, `t1`.`repairType`, `t1`.`repairTypeRus`, `t1`.`SparePartsConsumption`,  `t1`.`RepairTypePrice`, `t1`.`SparePartsPrice`, `t3`.`currency` as `currency`, `t4`.`name` as `customer`, `t1`.`model` FROM `repair_type` as `t1`, `currency` as t3, `customer` as t4 WHERE `t1`.`currency` = `t3`.`id` AND `customer` = `t4`.`id` AND `customer` = '%s' AND `t1`.`model` in (SELECT `mrtid` FROM `mrtmodel` WHERE `modelid` = %s)"
query_getSparePartsConsumption = "SELECT `t1`.`id`, `t1`.`guid`, `t2`.`description` as `spcode`, `t1`.`quantity` FROM `spcodetable` as `t1`, `spare_parts` as `t2` WHERE `t1`.`spcode` = `t2`.`id`"
query_getCustomer = "SELECT `t1`.`id`, `t1`.`name`, `t2`.`group` FROM `customer` as `t1`, `customer_group` as `t2` WHERE `t1`.`group` = `t2`.`id`;"
query_getDModel = "SELECT * FROM `model`;"
query_getDDestination = "SELECT * FROM `destination`;"
query_getTransactionType = "SELECT * FROM  `transaction_type`;"
query_autoTransactionType = "SHOW TABLE STATUS FROM `" + database + "` LIKE 'transaction_type';"
query_addTransactionType = "INSERT INTO `transaction_type` (`id`, `transactionType`, `transactionFormula`) VALUES (NULL , '%s', '%s');"
query_addInventoryAdjustment = "INSERT INTO `inventory_adjustment` (`id`, `ItemCode`, `transactionType`, `quantity`, `transactionDate`) VALUES (NULL , '%s', '%s', '%s', '%s');"
query_addSPIntegerLi = "SELECT `integer` FROM `spare_parts` WHERE `id` = '%s'";
query_getTransactionByDate = "SELECT `t1`.`transactionDate`, `t2`.`code`, `t2`.`description`, `t3`.`transactionFormula`, SUM(`t1`.`quantity`) as `quantity`, `t3`.`id`, `t3`.`transactionType`  FROM `inventory_adjustment` as `t1`, `spare_parts` as`t2`, `transaction_type` as `t3` WHERE `t1`.`ItemCode` = `t2`.`id` AND `t1`.`transactionType` = `t3`.`id` AND `t1`.`transactionDate` >= '%s' AND `t1`.`transactionDate` <= '%s' AND `t2`.`code` >= '%s' AND `t2`.`code` <= '%s' AND `t3`.`id` >= '%s' AND `t3`.`id` <= '%s' GROUP BY `t2`.`code`, `t3`.`transactionType` ORDER BY  `t1`.`transactionDate` ASC;"
query_getTransactionByDateDetail = "SELECT `t1`.`transactionDate`, `t2`.`code`, `t2`.`description`, `t3`.`transactionFormula`, `t1`.`quantity`, `t3`.`id`, `t3`.`transactionType`  FROM `inventory_adjustment` as `t1`, `spare_parts` as`t2`, `transaction_type` as `t3` WHERE `t1`.`ItemCode` = `t2`.`id` AND `t1`.`transactionType` = `t3`.`id` AND `t1`.`transactionDate` >= '%s' AND `t1`.`transactionDate` <= '%s' AND `t2`.`code` >= '%s' AND `t2`.`code` <= '%s' AND `t3`.`id` >= '%s' AND `t3`.`id` <= '%s';"
query_getWarehouseInventoryByDate0 = "SELECT * FROM (SELECT `t1`.`code`, `t1`.`description`, `t1`.`safety_stock`, (COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1, `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '1' AND `s1`.`transactionDate` <= '%s'), 0)-COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1, `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '0' AND `s1`.`transactionDate` <= '%s'), 0)) as `quantity` FROM `spare_parts` as `t1`) as `f1` WHERE `f1`.`quantity` >= '%s';"
query_getWarehouseInventoryByDate1 = "SELECT * FROM (SELECT `t1`.`code`, `t1`.`description`, `t1`.`safety_stock`, (COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1, `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '1' AND `s1`.`transactionDate` <= '%s'), 0)-COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1, `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '0' AND `s1`.`transactionDate` <= '%s'), 0)) as `quantity` FROM `spare_parts` as `t1`) as `f1` WHERE `f1`.`quantity` >= '%s' AND `f1`.`quantity` >= `f1`.`safety_stock`;"
query_getWarehouseInventoryByItem = "SELECT `res`.`code`, (SELECT `description` FROM `spare_parts` WHERE `code` = `res`.`code`) as `description`, COALESCE(`res`.`quantity`, 0) as `quantity`, CEILING(`res`.`count` * (`res`.`planning_condition` / 100)) as `allocated`, `res`.`safety_stock` FROM (SELECT `w1`.`code`, `w2`.`description`, `w2`.`quantity`, `w1`.`count`, `w1`.`safety_stock`, `w1`.`planning_condition` FROM (SELECT `q2`.`code`, SUM(`q3`.`count`) as `count`, `q2`.`safety_stock`, `q2`.`planning_condition` FROM (`model_swap` as `q1` LEFT JOIN `spare_parts` as `q2` ON (`q1`.`select_id` = `q2`.`model`)) LEFT JOIN (SELECT `t1`.`model`, COUNT(`t1`.`model`) as `count` FROM `cooler_receipt` as `t1` LEFT JOIN `repair_note` as `t2` ON (`t1`.`id` = `t2`.`cooler`) WHERE `t2`.`repairdate` IS NULL GROUP BY `t1`.`model`) as `q3` ON (`q1`.`model_id` = `q3`.`model`) WHERE `q2`.`id` IS NOT NULL GROUP BY (`q2`.`id`)) as `w1`  LEFT JOIN (SELECT `t7`.`code`, `t7`.`description`, `t6`.`quantity` FROM (SELECT `t4`.`ItemCode`, (COALESCE(`t4`.`quantity`, 0) - COALESCE(`t5`.`quantity`, 0)) as `quantity` FROM (SELECT `t1`.`id`, `t1`.`ItemCode`, SUM(`t1`.`quantity`) as `quantity`, `t2`.`transactionFormula` FROM `inventory_adjustment` as `t1` LEFT JOIN `transaction_type` as `t2` ON (`t1`.`transactionType` = `t2`.`id`) WHERE `t2`.`transactionFormula` = 1 AND `t1`.`transactionDate` <= '%s' GROUP BY `t1`.`ItemCode`) as `t4` LEFT JOIN (SELECT `t1`.`id`, `t1`.`ItemCode`, SUM(`t1`.`quantity`) as `quantity`, `t2`.`transactionFormula` FROM `inventory_adjustment` as `t1` LEFT JOIN `transaction_type` as `t2` ON (`t1`.`transactionType` = `t2`.`id`) WHERE `t2`.`transactionFormula` = 0 AND `t1`.`transactionDate` <= '%s' GROUP BY `t1`.`ItemCode`) as `t5` ON (`t4`.`ItemCode` = `t5`.`ItemCode`)) as `t6`  LEFT JOIN `spare_parts` as `t7` ON (`t6`.`ItemCode` = `t7`.`id`)) as `w2` ON (`w1`.`code` = `w2`.`code`)) as `res` WHERE `res`.`code` = '%s';"
query_getZeroQuantity = "SELECT * FROM (SELECT `t1`.`code`, `t1`.`description`, `t1`.`safety_stock`, (COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1,  `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '1'), 0)-COALESCE((SELECT SUM(`s1`.`quantity`) FROM `inventory_adjustment` as s1, `transaction_type` as `s2` WHERE `s1`.`ItemCode` = `t1`.`id` AND `s1`.`transactionType` = `s2`.`id` AND `s2`.`transactionFormula` = '0'), 0)) as `quantity` FROM `spare_parts` as `t1`) as `f1` WHERE `f1`.`quantity` <= '0';";
query_getReapairType = "SELECT * FROM `repair_type`"
query_setReapairNote = "INSERT INTO `repair_note` (`id`, `repairnumber`, `cooler`, `repairdate`, `brand`, `dispatchdate`, `dispatchnote`, `specialnote`, `repairtype`, `transactiontype`, `otherspareparts`) VALUES (NULL, '%s', '%s', '%s', '%s', %s, %s, %s, '%s', '%s', '%s');"
query_setReapairNoteUpdate = "UPDATE `repair_note` SET `cooler` = '%s', `repairdate` = '%s', `brand` = '%s', `dispatchdate` = %s, `dispatchnote` = %s, `specialnote` = %s, `repairtype` = '%s', `transactiontype` = '%s', `otherspareparts` = '%s' WHERE `repairnumber` = '%s';"
query_setReapairNoteUpdateFix = "UPDATE `repair_note` SET `repairdate` = '%s', `brand` = '%s', `dispatchdate` = %s, `dispatchnote` = %s, `specialnote` = %s, `repairtype` = '%s', `transactiontype` = '%s', `otherspareparts` = '%s' WHERE `repairnumber` = '%s';"
query_getRepairNumber = "SHOW TABLE STATUS FROM `" + database + "` LIKE 'repair_note';"
query_getConsumptionRepairCode = "SELECT `id` FROM `transaction_type` WHERE `transactionType` = 'Consumption Repair'"
query_getRepairNoteTable = "SELECT `t1`.`id`, `t1`.`repairnumber`, `t1`.`repairdate`, `t1`.`brand`, `t1`.`dispatchdate`, `t1`.`dispatchnote`, `t1`.`specialnote`, `t1`.`transactiontype`, `t1`.`otherspareparts`, `t3`.`name` as `customer`, `t5`.`daddr` as `destination`, `t4`.`name` as `model`, `t2`.`snumber`, `t2`.`inumber`, `t2`.`receipt_date`  FROM `repair_note` as `t1`, `cooler_receipt` as `t2`, `customer` as `t3`, `model` as `t4`, `destination` as `t5` WHERE `t1`.`cooler` = `t2`.`id` AND `t3`.`id` = `t2`.`customer` AND `t4`.`id` = `t2`.`model` AND `t5`.`id` = `t2`.`destination`;"
query_getRepairItem = "SELECT `t1`.`id`, `t1`.`repairnumber`, `t1`.`cooler`, `t1`.`repairdate`, `t1`.`brand`, `t1`.`dispatchdate`, `t1`.`dispatchnote`, `t1`.`specialnote`, `t1`.`repairtype`, `t1`.`transactiontype`, `t1`.`otherspareparts`, `t2`.snumber  FROM `repair_note` as `t1`, `cooler_receipt` as `t2` WHERE `t1`.`id` = '%s' AND `t1`.`cooler` = `t2`.`id`;"
query_getRepairNoteTableReport = "SELECT  `t1`.`id` ,  `t1`.`repairnumber` ,  `t2`.`name` AS  `customer` ,  `t1`.`repairdate` ,  `t3`.`name` AS  `model` ,  `t5`.`snumber` AS `serialnumber` ,  `t5`.`inumber` AS `inventorynumber` ,  `t4`.`daddr` AS  `destination` ,  `t1`.`dispatchnote`, `t1`.`dispatchdate` FROM  `repair_note` AS  `t1` ,  `customer` AS  `t2` ,  `model` AS  `t3` ,  `destination` AS  `t4`, `cooler_receipt` AS `t5` WHERE  `t5`.`customer` =  `t2`.`id`   AND  `t5`.`model` =  `t3`.`id`  AND  `t5`.`destination` =  `t4`.`id` AND  `t1`.`cooler` = `t5`.`id` AND  `%s` =  '%s';"
query_getRepairNoteTableReportRTCode = "SELECT `t1`.`id` ,  `t1`.`repairnumber` ,  `t2`.`name` AS  `customer` ,  `t1`.`repairdate` ,  `t3`.`name` AS  `model` ,  `t5`.`snumber` AS `serialnumber` ,  `t5`.`inumber` AS `inventorynumber` ,  `t4`.`daddr` AS  `destination` ,  `t1`.`dispatchnote`, `t1`.`dispatchdate` FROM  `repair_note` AS  `t1` ,  `customer` AS  `t2` ,  `model` AS  `t3` ,  `destination` AS  `t4`, `cooler_receipt` AS `t5`, `rtcodetable` as `t6` WHERE  `t5`.`customer` =  `t2`.`id` AND  `t5`.`model` =  `t3`.`id` AND  `t5`.`destination` =  `t4`.`id` AND  `t1`.`cooler` = `t5`.`id` AND  `t1`.`repairtype` = `t6`.`guid` AND `t6`.`rtcode` = '%s';"
query_getGroupPricing = "SELECT `t3`.`code`, `t3`.`description`, `t3`.`group`, COALESCE(`t4`.`pricing`, 0) as `gprice` FROM (SELECT `t1`.`code`, `t1`.`description`, `t2`.`group`, `t2`.`id` FROM `spare_parts` as `t1`, `customer_group` as `t2`) as `t3` LEFT JOIN `pricing` as `t4` ON (`t3`.`code` = `t4`.`spcode` AND `t3`.`id` = `t4`.`group`);"
query_getGroupPricingZero = "SELECT * FROM (SELECT `t3`.`code`, `t3`.`description`, `t3`.`group`, COALESCE(`t4`.`pricing`, 0) as `gprice` FROM (SELECT `t1`.`code`, `t1`.`description`, `t2`.`group`, `t2`.`id` FROM `spare_parts` as `t1`, `customer_group` as `t2`) as `t3` LEFT JOIN `pricing` as `t4` ON (`t3`.`code` = `t4`.`spcode` AND `t3`.`id` = `t4`.`group`)) as `w1` WHERE `w1`.`gprice` = '0';"
query_updateGroupPrace = "UPDATE `pricing` SET `pricing` = '%s' WHERE `spcode` = '%s' AND `group` = '%s';"
query_insertGroupPrace = "INSERT INTO `pricing` (`id`, `spcode`, `group`, `pricing`) VALUES (NULL, '%s', '%s', '%s');"
query_getGroupIdByName = "SELECT `id` FROM `customer_group` WHERE `group` = '%s';"
query_getGroupPriceLine = "SELECT `id` FROM `pricing` WHERE `spcode` = '%s' AND `group` = '%s';"
query_getPrintDetailedInvoicebyPeriod = "SELECT `t1`.`repairnumber`, `t1`.`dispatchdate`,(SELECT `daddr` FROM `destination` WHERE `id` = `t2`.`destination`) as `destination`, `t2`.`snumber`, `t2`.`inumber`, `t5`.`name` as `model`, `t1`.`brand`, `t4`.`repairTypeRus`, `t4`.`RepairTypePrice`, `t4`.`SparePartsPrice`, (SELECT SUM(`q1`.`quantity` * COALESCE(`q4`.`pricing`, 0)) as `price` FROM (`spcodetable` as `q1`, `spare_parts` as `q2`, `customer` as `q3`) LEFT JOIN `pricing` as `q4` ON (`q3`.`group` = `q4`.`group` AND `q2`.`code` = `q4`.`spcode`) WHERE `q1`.`spcode` = `q2`.`id` AND `q1`.`guid` = `t1`.`otherspareparts` AND `q3`.`id` = `t2`.`customer`) as `price` FROM (((`repair_note` as `t1` LEFT JOIN `cooler_receipt` as `t2` ON (`t1`.`cooler` = `t2`.`id`)) LEFT JOIN `rtcodetable` as `t3` ON (`t1`.`repairtype` = `t3`.`guid`)) LEFT JOIN `repair_type` as `t4` ON (`t3`.`rtcode` = `t4`.`code`)) LEFT JOIN `model` as `t5` ON (`t2`.`model` = `t5`.`id`) WHERE `t1`.`dispatchdate` >= '%s' AND `t1`.`dispatchdate` <= '%s' %s;"
query_getPrintInvoicebyModel = "select `a1`.`model`, COUNT(`a1`.`model`) as `quantity`, SUM(`a1`.`RepairTypePrice`) as `rtprice`, SUM(`a1`.`SparePartsPrice`) as `spprice`, SUM(`a1`.`gprice`) as `gprice` FROM (SELECT `t2`.`name` as `model`, `t3`.`repairTypeRus`, `t3`.`RepairTypePrice`, `t3`.`SparePartsPrice`, `t4`.`gprice` FROM `repair_note` as `t1`, `model` as `t2`, `repair_type` as `t3`, (select `r1`.`guid`, SUM(`r2`.`pricing`) as `gprice` FROM (select `e1`.`guid`, `e1`.`quantity`, `e1`.`code`, `e1`.`description`, `e1`.`price`, `e1`.`customer`, `e2`.`group` FROM (select `w1`.`guid`, `w1`.`quantity`, `w1`.`code`, `w1`.`description`, `w1`.`price`, `w2`.`customer` FROM (SELECT `q1`.`guid`, `q1`.`quantity`, `q2`.`code`, `q2`.`description`, `q2`.`price`, `q2`.`id` FROM `spcodetable` as `q1`, `spare_parts` as `q2` WHERE `q1`.`spcode` = `q2`.`id`) as `w1` LEFT JOIN `repair_note` as `w2` ON (`w1`.`guid` = `w2`.`otherspareparts`)) as `e1`, `customer` as `e2` WHERE `e1`.`customer` IS NOT NULL AND `e1`.`customer` = `e2`.`id`) as `r1`, `pricing` as `r2` WHERE `r1`.`code` = `r2`.`spcode` AND `r1`.`group` = `r2`.`group` GROUP BY `r1`.`guid`) as `t4` WHERE `t1`.`model` = `t2`.`id` AND `t1`.`repairtype` = `t3`.`code` AND `t1`.`otherspareparts` = `t4`.`guid`) as `a1` GROUP BY `a1`.`model`;"
query_getRNote = "SELECT `id`, `repairnumber` FROM `repair_note`;"
query_getPrintInvoicebyRepairType = "SELECT `t2`.`repairTypeRus`, `t2`.`RepairTypePrice`, `t2`.`SparePartsPrice` FROM `repair_note` as `t1`, `repair_type` as `t2` WHERE `t1`.`repairtype` = `t2`.`code` AND `t1`.`id` = '%s';"
query_getPrintOtherSparePartsperRepairNote = "SELECT  `q1`.`customer`, `q1`.`dispatchnote`, `q1`.`dispatchdate`, `q1`.`repairnumber`, `q1`.`snumber`, (SELECT `daddr` FROM `destination` WHERE `id` = `q1`.`destination`) as `destination`, `q1`.`inumber`, `q1`.`model` as `model`,`q1`.`repairdate`, `q1`.`brand`, `q1`.`repairTypeRus`, `q1`.`rtprice`, `q1`.`spprice`, `q3`.`code`, `q3`.`description_rus`, `q2`.`quantity`, COALESCE((SELECT `w1`.`pricing` FROM `pricing` as `w1` LEFT JOIN `customer` as `w2` ON (`w1`.`group` = `w2`.`group`) WHERE `w1`.`spcode` = `q3`.`code` AND `w2`.`id` = `q1`.`customer`), 0) as `pricing` FROM ((SELECT `t1`.`repairnumber`, `t2`.`snumber`, `t2`.`inumber`, `t2`.`destination`,`t2`.`customer`, `t5`.`name` as `model`, `t1`.`repairdate`, `t1`.`dispatchdate`, `t1`.`dispatchnote`, `t1`.`brand`, `t4`.`repairTypeRus`, SUM(`t4`.`RepairTypePrice`) as `rtprice`, SUM(`t4`.`SparePartsPrice`) as `spprice`, `t1`.`otherspareparts` FROM ((((`repair_note` as `t1` LEFT JOIN `cooler_receipt` as `t2` ON (`t1`.`cooler` = `t2`.`id`)) LEFT JOIN `rtcodetable` as `t3` ON (`t1`.`repairtype` = `t3`.`guid`)) LEFT JOIN `repair_type` as `t4` ON (`t3`.`rtcode` = `t4`.`code`)) LEFT JOIN `model` as `t5` ON (`t2`.`model` = `t5`.`id`)) GROUP BY (`t1`.`repairnumber`)) as `q1`  LEFT JOIN `spcodetable` as `q2` ON (`q1`.`otherspareparts` = `q2`.`guid`)) LEFT JOIN `spare_parts` as `q3` ON (`q2`.`spcode` = `q3`.`id`) WHERE %s;"
query_getPrintRepairNoteNumber1 = "SELECT `q1`.`repairnumber`, `q1`.`snumber`, `q1`.`inumber`, (SELECT `e1`.`name` FROM `customer` as `e1` WHERE `e1`.`id` = `q1`.`customer`) as `customer`, `q1`.`model` as `model`,`q1`.`repairdate`, `q1`.`brand`, `q1`.`repairTypeRus`, `q1`.`rtprice`, `q1`.`spprice`, `q3`.`code`, `q3`.`description_rus`, `q2`.`quantity`, COALESCE((SELECT `w1`.`pricing` FROM `pricing` as `w1` LEFT JOIN `customer` as `w2` ON (`w1`.`group` = `w2`.`group`) WHERE `w1`.`spcode` = `q3`.`code` AND `w2`.`id` = `q1`.`customer`), 0) as `pricing` FROM ((SELECT `t1`.`id`, `t1`.`repairnumber`, `t2`.`snumber`, `t2`.`inumber`, `t2`.`customer`, `t5`.`name` as `model`, `t1`.`repairdate`, `t1`.`brand`, `t4`.`repairTypeRus`, SUM(`t4`.`RepairTypePrice`) as `rtprice`, SUM(`t4`.`SparePartsPrice`) as `spprice`, `t1`.`otherspareparts` FROM ((((`repair_note` as `t1` LEFT JOIN `cooler_receipt` as `t2` ON (`t1`.`cooler` = `t2`.`id`)) LEFT JOIN `rtcodetable` as `t3` ON (`t1`.`repairtype` = `t3`.`guid`)) LEFT JOIN `repair_type` as `t4` ON (`t3`.`rtcode` = `t4`.`code`)) LEFT JOIN `model` as `t5` ON (`t2`.`model` = `t5`.`id`)) GROUP BY (`t1`.`repairnumber`)) as `q1`  LEFT JOIN `spcodetable` as `q2` ON (`q1`.`otherspareparts` = `q2`.`guid`)) LEFT JOIN `spare_parts` as `q3` ON (`q2`.`spcode` = `q3`.`id`) WHERE `q1`.`id` = '%s';"
query_getPrintRepairNoteNumber2 = "SELECT `t2`.`repairnumber`, `t5`.`snumber`, `t5`.`inumber`, (SELECT `name` FROM `customer` WHERE `id` = `t5`.`customer`) as `customer`, (SELECT `name` FROM `model` WHERE `id` = `t5`.`model`) as `model`, `t2`.`repairdate`, `t2`.`brand`, `t3`.`repairTypeRus`, `t3`.`RepairTypePrice`, `t3`.`SparePartsPrice`, `t6`.`code`, `t6`.`description_rus`, `t4`.`quantity`, COALESCE((SELECT `w1`.`pricing` FROM `pricing` as `w1` LEFT JOIN `customer` as `w2` ON (`w1`.`group` = `w2`.`group`) WHERE `w1`.`spcode` = `t6`.`code` AND `w2`.`id` = `t5`.`customer`), 0) as `pricing` FROM ((`rtcodetable` as `t1` LEFT JOIN `repair_note` as `t2` ON (`t1`.`guid` = `t2`.`repairtype`) LEFT JOIN `repair_type` as `t3` ON (`t1`.`rtcode` = `t3`.`code`)  RIGHT JOIN `spcodetable` as `t4` ON (`t4`.`guid` = `t3`.`SparePartsConsumption`)) LEFT JOIN `cooler_receipt` as `t5` ON (`t2`.`cooler` = `t5`.`id`)) LEFT JOIN `spare_parts` as `t6` ON (`t6`.`id` = `t4`.`spcode`) WHERE `t2`.`id` = '%s';"
query_insertCoolerRecept = "INSERT INTO `cooler_receipt` (`id`, `customer`, `destination`, `model`, `snumber`, `inumber`, `receipt_date`) VALUES (NULL ,  '%s', '%s',  '%s',  '%s',  '%s',  '%s');"
query_updateCoolerRecept = "UPDATE `cooler_receipt` SET `customer` = '%s', `destination` = '%s', `model` = '%s', `snumber` = '%s', receipt_date = '%s' WHERE `inumber` = '%s';"
query_getCoolerInventory = "SELECT * FROM (SELECT `q1`.`id`, `q3`.`name` AS `customer`, `q4`.`daddr` AS `destination`, `q5`.`name` AS `model`, `q1`.`snumber`, `q1`.`inumber`, `q1`.`receipt_date`, COALESCE(`q2`.`repairdate`, '') AS `repaitdate`, COALESCE(`q2`.`Dispatchdate`, '') AS `dispatchdate` FROM `cooler_receipt` as `q1` LEFT JOIN `repair_note` as `q2` ON (`q1`.`id` = `q2`.`cooler`), `customer` as `q3`, `destination` AS `q4`, `model` AS `q5` WHERE `q1`.`customer` = `q3`.`id` AND `q1`.`destination` = `q4`.`id` AND `q1`.`model` = `q5`.`id`) as `f1` WHERE %s;"
query_getSPNeedOrder = "SELECT `res`.`code`, `res`.`description`, CEILING((`res`.`count` * (`res`.`planning_condition` / 100)) + `res`.`safety_stock`) as `qty_to_order` FROM (SELECT `w1`.`code`, `w2`.`description`, `w2`.`quantity`, `w1`.`count`, `w1`.`safety_stock`, `w1`.`planning_condition` FROM (SELECT `q2`.`code`, SUM(`q3`.`count`) as `count`, `q2`.`safety_stock`, `q2`.`planning_condition` FROM (`model_swap` as `q1` LEFT JOIN `spare_parts` as `q2` ON (`q1`.`select_id` = `q2`.`model`)) LEFT JOIN (SELECT `t1`.`model`, COUNT(`t1`.`model`) as `count` FROM `cooler_receipt` as `t1` LEFT JOIN `repair_note` as `t2` ON (`t1`.`id` = `t2`.`cooler`) WHERE `t2`.`repairdate` IS NULL GROUP BY `t1`.`model`) as `q3` ON (`q1`.`model_id` = `q3`.`model`) WHERE `q2`.`id` IS NOT NULL GROUP BY (`q2`.`id`)) as `w1`  LEFT JOIN (SELECT `t7`.`code`, `t7`.`description`, `t6`.`quantity` FROM (SELECT `t4`.`ItemCode`, (COALESCE(`t4`.`quantity`, 0) - COALESCE(`t5`.`quantity`, 0)) as `quantity` FROM (SELECT `t1`.`id`, `t1`.`ItemCode`, SUM(`t1`.`quantity`) as `quantity`, `t2`.`transactionFormula` FROM `inventory_adjustment` as `t1` LEFT JOIN `transaction_type` as `t2` ON (`t1`.`transactionType` = `t2`.`id`) WHERE `t2`.`transactionFormula` = 1 GROUP BY `t1`.`ItemCode`) as `t4` LEFT JOIN (SELECT `t1`.`id`, `t1`.`ItemCode`, SUM(`t1`.`quantity`) as `quantity`, `t2`.`transactionFormula` FROM `inventory_adjustment` as `t1` LEFT JOIN `transaction_type` as `t2` ON (`t1`.`transactionType` = `t2`.`id`) WHERE `t2`.`transactionFormula` = 0 GROUP BY `t1`.`ItemCode`) as `t5` ON (`t4`.`ItemCode` = `t5`.`ItemCode`)) as `t6`  LEFT JOIN `spare_parts` as `t7` ON (`t6`.`ItemCode` = `t7`.`id`)) as `w2` ON (`w1`.`code` = `w2`.`code`)) as `res`;"
query_insertSPOrder = "INSERT INTO `spare_parts_order` (`id`, `spcode`, `quantity`, `date`) VALUES (NULL, '%s', '%s', '%s');"
query_idbyspcode = "SELECT `id` FROM `spare_parts` WHERE `code`='%s';"
query_getSPNeedOrderTable = "SELECT `t1`.`id`, `t2`.`code`, `t2`.`description`, `t1`.`quantity`, `t1`.`date` FROM `spare_parts_order` as `t1`, `spare_parts` as `t2` WHERE `qtyclose` IS NULL AND `t1`.`spcode` = `t2`.`id`;"
query_getCloseSPNeedOrderTable = "SELECT `t1`.`id`, `t2`.`code`, `t2`.`description`, `t1`.`qtyclose` as `quantity`, `t1`.`qtydate` as `date` FROM `spare_parts_order` as `t1`, `spare_parts` as `t2` WHERE `qtyclose` IS NOT NULL AND `t1`.`spcode` = `t2`.`id`;"
query_closeOrderById = "UPDATE `spare_parts_order` SET  `qtyclose` =  '%s', `qtydate` =  CURDATE() WHERE  `spare_parts_order`.`id` = '%s';"
query_addRTCode = "INSERT INTO `rtcodetable` (`id`, `guid`, `rtcode`) VALUES (NULL, '%s', '%s');"
query_rtcodetable = "SELECT `t1`.`id` as `id`, `t2`.`repairType` FROM `rtcodetable` as t1, `repair_type` as t2 WHERE `t1`.`rtcode` = `t2`.`code` AND `t1`.`guid` = '%s';"
query_delRTCodeByID = "DELETE FROM `rtcodetable` WHERE `id` = '%s';"
query_returnCooler = "DELETE FROM `cooler_receipt` WHERE `cooler_receipt`.`id` = '%s';"
query_getIsIntegerBySPCode = "SELECT `integer` FROM `spare_parts` WHERE `id` = '%s';"
query_getCoolerSNFromCoolerReceipt = "SELECT `t1`.`id`, `t1`.`snumber` FROM `cooler_receipt` as `t1` LEFT JOIN `repair_note` as `t2` ON (`t1`.`id` = `t2`.`cooler`) WHERE `t2`.`dispatchdate` IS NULL;"
query_getCoolerById = "SELECT * FROM `cooler_receipt` as `t1` WHERE `t1`.`id` = '%s';"
query_delAllTransactionByHash = "DELETE FROM `inventory_adjustment` WHERE `transactionHash` = '%s' AND `transactionHash` IS NOT NULL;"
query_getSparePartsByGUID = "SELECT `spcode`, `quantity` FROM `spcodetable` WHERE `guid` = '%s';"
query_insertAutoTransactionOSP = "INSERT INTO `inventory_adjustment` (`id`, `ItemCode`, `transactionType`, `quantity`, `transactionDate`, `transactionhash`) VALUES (NULL, '%s', '%s', '%s', '%s', '%s');"
query_getListRTbyGUID = "SELECT `rtcode` FROM `rtcodetable` WHERE `guid` = '%s';"
query_getAutoTransactionRTL = "SELECT `t1`.`spcode`, `t1`.`quantity` FROM `spcodetable` as `t1` LEFT JOIN `repair_type` as `t2` ON (`t1`.`guid` = `t2`.`SparePartsConsumption`) WHERE `t2`.`code` = '%s';"
query_getDispatchDateById = "SELECT `t1`.`id`, `t2`.`repairdate` FROM `cooler_receipt` as `t1` LEFT JOIN `repair_note` as `t2` ON (`t1`.`id` = `t2`.`cooler`) WHERE `t1`.`id` = '%s';"
query_getModelRepairCount = "SELECT COUNT(`t2`.`model`) as `countmodel`, (SELECT `t3`.`name` as `model` FROM `model` as `t3` WHERE `t3`.`id` = `t2`.`model`) as `model` FROM `repair_note` as `t1` LEFT JOIN `cooler_receipt` as `t2` ON (`t1`.`cooler` = `t2`.`id`)  WHERE %s AND `t1`.`dispatchdate` IS NOT NULL GROUP BY `t2`.`model`;"
query_getRTItem = "SELECT * FROM `repair_type` WHERE `code` = '%s';"
query_getRTGUIDByRTCode = ("SELECT `SparePartsConsumption` as `GUID` FROM"
                           " `repair_type` WHERE `code` = '%s';")
query_updateRTRecord = ("UPDATE `repair_type` SET `currency` = '%s',"
                        " `SparePartsPrice` = '%s', `RepairTypePrice` = '%s',"
                        " `model` = '%s', `SparePartsConsumption` = '%s',"
                        " `repairTypeRus` = '%s', `repairType` = '%s',"
                        " `customer` = '%s' WHERE  `code` = '%s' LIMIT 1 ;")
